﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Jumpton.SFA.Server.Entity;
using Jumpton.Persistent;

namespace Jumpton.SFA.Server.BusinessRule
{
    public class MobileUserManager : BusinessLogic
    {

        #region 保存
        public bool Save(MOBILE_USERS mobileUser)
        {
            bool success = false;
            if (mobileUser != null)
            {
                try
                {
                    mobileUser.Save();
                    success = true;
                }
                catch (Exception error)
                {
                    throw new Exception(error.Message);
                }
            }
            return success;
        }
        #endregion

        #region 根据用户ID读取
        public MOBILE_USERS Load(decimal userID, decimal domainID)
        {
            return (MOBILE_USERS)ObjectRow.Load(typeof(MOBILE_USERS), "this.DOMAINID='" + domainID + "' and this.USERID='" + userID + "'");
        }
        #endregion

       


        public DataTable Load(string name, decimal domainID, int orgID)
        {
            string sql = @"select distinct USERS.ID,USERS.CODE 'USER',DictionaryItems.Name 'TYPE',admin.CODE 'MANAGER',T1.Name 'GEO_LEVEL'
		                            ,PERSONS.Name AS CODENAME
                            from MOBILE_USERS
	                            inner join USERS on MOBILE_USERS.USER_ID=USERS.ID
	                            left outer join DictionaryItems on MOBILE_USERS.type=DictionaryItems.DictionaryItemID
	                            left join USERS admin on MOBILE_USERS.MANAGER_ID=admin.ID
	                            left outer join DictionaryItems t1 on MOBILE_USERS.GEO_LEVEL_CODE=DictionaryItems.DictionaryItemID
                                inner join PERSONS ON PERSONS.ID = USERS.PERSON_ID
                            where MOBILE_USERS.DOMAIN_ID='" + domainID + @"'
                                AND USERS.ORG_ID IN (select ID from [FC_GetOrgWithParentID]('" + domainID + "','" + orgID + @"'))
                            ORDER BY USERS.CODE";



            if (!string.IsNullOrEmpty(name))
                sql += " and USERS.CODE like '%" + name + "%'";

            return Jumpton.Data.SqlData.OpenSql(sql).Tables[0];
        }

        public DataTable Load(string whereCondition)
        {
            string SQL = string.Format(@"SELECT MU.USER_ID,U.CODE,DI.NAME AS POSITION,P.NAME,P.TELEPHONE 
                                        FROM MOBILE_USERS MU
                                        INNER JOIN USERS U ON U.ID = MU.USER_ID
                                        INNER JOIN PERSONS P ON P.ID = U.PERSON_ID
                                        LEFT JOIN DICTIONARYITEMS DI ON DI.DICTIONARYITEMID = P.TYPE
                                        Where {0}", whereCondition);

            return Jumpton.Data.SqlData.OpenSql(SQL).Tables[0];
        }

        public DataTable LoadByChains(decimal domainID, int chainsID)
        {
            string sql = @"SELECT DISTINCT P.NAME 'CODENAME' , U.ID 'ID'
                        FROM CUSTOMER_CHAINS  CC 
                        INNER JOIN ROUTE_CUSTOMERS RC ON CC.CUSTOMER_ID = RC.CUSTOMER_ID
                        INNER JOIN USERS U ON RC.USER_ID = U.ID
                        INNER JOIN PERSONS P ON U.PERSON_ID=P.ID
                        WHERE U.DOMAIN_ID=" + domainID.ToString() + " AND CC.CHAINS_ID=" + chainsID.ToString();
            return Jumpton.Data.SqlData.OpenSql(sql).Tables[0];
        }

        public DataTable Load(decimal domainID, string orgID)
        {
            string sql = @"select distinct USERS.ID,USERS.CODE 'USER',DictionaryItems.Name 'TYPE',admin.CODE 'MANAGER',T1.Name 'GEO_LEVEL'
		                            ,PERSONS.Name AS CODENAME
                            from MOBILE_USERS
	                            inner join USERS on MOBILE_USERS.USER_ID=USERS.ID
	                            left outer join DictionaryItems on MOBILE_USERS.type=DictionaryItems.DictionaryItemID
	                            left join USERS admin on MOBILE_USERS.MANAGER_ID=admin.ID
	                            left outer join DictionaryItems t1 on MOBILE_USERS.GEO_LEVEL_CODE=DictionaryItems.DictionaryItemID
                                inner join PERSONS ON PERSONS.ID = USERS.PERSON_ID
                            where MOBILE_USERS.DOMAIN_ID='" + domainID + @"'
                                AND USERS.ORG_ID IN (" + orgID + @")
                            ORDER BY PERSONS.Name";


            return Jumpton.Data.SqlData.OpenSql(sql).Tables[0];
        }

        public DataTable Load(decimal domainID, string orgID,string selfid)
        {
            string sql = @"select distinct USERS.ID,USERS.CODE 'USER',DictionaryItems.Name 'TYPE',admin.CODE 'MANAGER',T1.Name 'GEO_LEVEL'
		                            ,PERSONS.Name AS CODENAME
                            from MOBILE_USERS
	                            inner join USERS on MOBILE_USERS.USER_ID=USERS.ID
	                            left outer join DictionaryItems on MOBILE_USERS.type=DictionaryItems.DictionaryItemID
	                            left join USERS admin on MOBILE_USERS.MANAGER_ID=admin.ID
	                            left outer join DictionaryItems t1 on MOBILE_USERS.GEO_LEVEL_CODE=DictionaryItems.DictionaryItemID
                                inner join PERSONS ON PERSONS.ID = USERS.PERSON_ID
                            where MOBILE_USERS.DOMAIN_ID='" + domainID + @"'
                                AND USERS.ORG_ID IN (" + orgID + @") and (PERSONS.MANAGER_ID='" + selfid + "' or PERSONS.ID='" + selfid + "') ORDER BY PERSONS.Name";


            return Jumpton.Data.SqlData.OpenSql(sql).Tables[0];
        }

        public static DataSet SelectManagerByOrg(decimal domainID, string orgID)
        {
            return Jumpton.Data.SqlData.OpenSP("sp_SelectManagerByOrg", domainID, orgID);
        }
        public static DataSet SelectManagerByOrgTree(decimal domainID, decimal orgID)
        {
            return Jumpton.Data.SqlData.OpenSP("sp_SelectManagerByOrgTree", domainID, orgID);
        }

        public static DataSet SelectUserDDLwithType(string whereCondition)
        {
            string SQL = string.Format(@"select distinct USERS.ID,USERS.CODE 'USER',DictionaryItems.Name 'TYPE',
                            admin.CODE 'MANAGER',T1.Name 'GEO_LEVEL'
                            ,PERSONS.Name AS CODENAME
                    from MOBILE_USERS
                        inner join USERS on MOBILE_USERS.USER_ID=USERS.ID
                        left outer join DictionaryItems on MOBILE_USERS.type=DictionaryItems.DictionaryItemID
                        left join USERS admin on MOBILE_USERS.MANAGER_ID=admin.ID
                        left outer join DictionaryItems t1 on MOBILE_USERS.GEO_LEVEL_CODE=DictionaryItems.DictionaryItemID
                        inner join PERSONS ON PERSONS.ID = USERS.PERSON_ID
                    where {0}  
                    ORDER BY USERS.CODE", whereCondition);

            return Jumpton.Data.SqlData.OpenSql(SQL);
        }

        public static DataSet SelectMOBILE_USERSsDynamic(string productListID,
                                                            string geoLevel,
                                                            string managerID,
                                                            decimal domainID,
                                                            string type,
                                                            string code,
                                                            string name,
                                                            string orgID)
        {
            return Jumpton.Data.SqlData.OpenSP("sp_SelectMOBILE_USERSsDynamic", productListID,
                                                                                geoLevel,
                                                                                managerID,
                                                                                domainID,
                                                                                type,
                                                                                code,
                                                                                name,
                                                                                orgID);
        }
    }
}
